# -*- coding=utf-8 -*-
# Author: Slp
# @Date : 2021-08-11


import openpyxl
import os


class ReadExcel:

    def getExcel(self, project_name):
        """

        :param project_name: Excel中对应项目sheet名称
        :return: 对应sheet中包含所有行值的list
        """

        sep = os.sep  # os.sep根据你所处的平台，自动采用相应的分隔符号(Windows分隔符是'\'，在Linux上是'/')
        try:
            # 获得项目根目录
            root_path = os.path.abspath(os.path.join(__file__, f"..{sep}..{sep}.."))
            # xlsx表格数据地址
            form_path = f"{root_path}{sep}data{sep}api_cases.xlsx"
            workbook = openpyxl.load_workbook(form_path)
            # workbook = xlrd.open_workbook(form_path)
            # 选择需要读取的sheet名称
            sheet = workbook[project_name]
            rows = sheet.iter_rows()  # 读取Excel表中所有的行数据
            list_excel = []
            for item in rows:
                if item[0].value == '环境':
                    continue
                list = []
                for col in item:  # 按列维度读取每行数据信息
                    cell = str(col.value)
                    # 将 header/body/预期结果中的 json格式的特殊数据类型转换为Python支持的数据类型
                    cell = cell.replace('null', 'None')
                    cell = cell.replace('false', 'False')
                    cell = cell.replace('true', 'True')
                    # print(cell)
                    list.append(cell)  # 获取到每一行的数据，存放列表中

                list_excel.append(list)  # 获取所有行数据，存放在列表中

            # print(list_excel)
            return list_excel

        except Exception as e:
            print(e)


if __name__ == '__main__':
    a = ReadExcel()
    a.getExcel('cwt_shop')
